package cn.zzuisa.news.repository.dao;

import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.zzuisa.news.entity.IprNewsInfo;
import cn.zzuisa.news.entity.IprNewsItem;
import cn.zzuisa.news.entity.IprNewsType;
import cn.zzuisa.news.entity.Page;
import cn.zzuisa.news.form.IprNewsInfoForm;
import cn.zzuisa.news.form.IprNewsItemForm;
import cn.zzuisa.news.form.NewsEditForm;
import cn.zzuisa.news.repository.IprNewsRepository;
import cn.zzuisa.news.util.JDBCUtil;

public class IprNewsDAO implements IprNewsRepository {

	@Override
	public boolean addType(String name) {

		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int flag = 0, maxId = 0;
		String maxid = "select max(id) id from ipr_news_type";
		String sql = "insert into `ipr_news_type` (type,name) values(?,?)";

		try {
			preparedStatement = conn.prepareStatement(maxid);
			preparedStatement.executeQuery();
			if (resultSet.next())
				maxId = resultSet.getInt("id") + 1;
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, maxId);
			preparedStatement.setString(2, name);
			flag = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

	@Override
	public boolean addNewsInfo(IprNewsInfoForm iprNewsInfoForm) {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int flag = 0;
		String sql = "insert into `ipr_news_info` (type,title,content,img) values(?,?,?,?)";

		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, iprNewsInfoForm.getType());
			preparedStatement.setString(2, iprNewsInfoForm.getTitle());
			preparedStatement.setString(3, iprNewsInfoForm.getContent());
			preparedStatement.setString(4, iprNewsInfoForm.getImg());
			flag = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

	@Override
	public boolean addNewsItem(IprNewsItemForm iprNewsItemForm) {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int flag = 0;
		String sql = "insert into `ipr_news_item` (type,title,content,state) values(?,?,?,?)";

		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, iprNewsItemForm.getType());
			preparedStatement.setString(2, iprNewsItemForm.getTitle());
			preparedStatement.setString(3, iprNewsItemForm.getContent());
			preparedStatement.setInt(4, 0);
			flag = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

	@Override
	public List<IprNewsType> listtype() {
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		// TODO Auto-generated method stub
		String sql = "select * from ipr_news_type";
		List<IprNewsType> list = new ArrayList<>();
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				IprNewsType iprNewsType = new IprNewsType();
				iprNewsType.setId(resultSet.getInt("id"));
				iprNewsType.setName(resultSet.getString("name"));
				iprNewsType.setType(resultSet.getInt("type"));
				list.add(iprNewsType);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public IprNewsType findIprNewsTypeById(Integer id) {
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		IprNewsType iprNewsType = new IprNewsType();
		try {
			String sql = "select * from ipr_news_type where id = " + id;
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				iprNewsType.setId(resultSet.getInt("id"));
				iprNewsType.setName(resultSet.getString("name"));
				iprNewsType.setType(resultSet.getInt("type"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// TODO Auto-generated method stub
		return iprNewsType;
	}

	@Override
	public List<IprNewsInfo> listinfoByType(Integer type) {
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		// TODO Auto-generated method stub
		String sql = type == null ? "select * from ipr_news_info" : "select * from ipr_news_info where type = " + type;
		List<IprNewsInfo> list = new ArrayList<>();
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				IprNewsInfo iprNewsInfo = new IprNewsInfo();
				iprNewsInfo.setId(resultSet.getInt("id"));
				iprNewsInfo.setTitle(resultSet.getString("title"));
				iprNewsInfo.setContent(resultSet.getString("content"));
				iprNewsInfo.setType(resultSet.getInt("type"));
				iprNewsInfo.setImg(resultSet.getString("img"));
				list.add(iprNewsInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public IprNewsInfo findInfoById(Integer id) {
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		// TODO Auto-generated method stub
		IprNewsInfo iprNewsInfo = new IprNewsInfo();
		String sql = "select * from ipr_news_info where id = " + id;
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {

				iprNewsInfo.setId(resultSet.getInt("id"));
				iprNewsInfo.setTitle(resultSet.getString("title"));
				iprNewsInfo.setContent(resultSet.getString("content"));
				iprNewsInfo.setType(resultSet.getInt("type"));
				iprNewsInfo.setImg(resultSet.getString("img"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return iprNewsInfo;
	}

	@Override
	public boolean editById(IprNewsInfoForm iprNewsInfoForm, Integer id) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		int flag = 0;
		String sql = "UPDATE ipr_news_info SET title =?, content=?, img=? where id = ?";
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, iprNewsInfoForm.getTitle());
			preparedStatement.setString(2, iprNewsInfoForm.getContent());
			preparedStatement.setString(3, iprNewsInfoForm.getImg());
			preparedStatement.setInt(4, id);
			flag = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

	@Override
	public boolean delete(Integer id) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		int flag = 0;
		String sql = "delete from ipr_news_info where id = ?";
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			flag = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

	@Override
	public List<String> showTypes() {
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = "select title from ipr_news_info";
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			List<String> types = new ArrayList<String>();
			while (resultSet.next()) {
				types.add(resultSet.getString("title"));
			}
			return types;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public List<String> showTypesId() {
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = "select type from ipr_news_info";
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			List<String> types = new ArrayList<String>();
			while (resultSet.next()) {
				types.add(resultSet.getString("type"));
			}
			return types;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public Page page(Integer current, Integer size, String sql) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Page page = null;
		sql = sql == null ? "" : sql;
		System.out.println("sqllll" + sql);
		String sql1 = "select count(id) from ipr_news_item where " + sql + " 1=1";
		String sql2 = "select * from `ipr_news_item` where " + sql + " 1=1 order by id desc limit ?,? ";
		try {
			int totalCount = 0;
			preparedStatement = conn.prepareStatement(sql1);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				totalCount = resultSet.getInt(1);
			}
			preparedStatement = conn.prepareStatement(sql2);
			preparedStatement.setInt(1, (current - 1) * size);
			preparedStatement.setInt(2, size);
			resultSet = preparedStatement.executeQuery();

			List<IprNewsItem> iprNewsItems = new ArrayList<>();
			while (resultSet.next()) {
				IprNewsItem iprNewsItem = new IprNewsItem();
				iprNewsItem.setId(resultSet.getInt("id"));
				iprNewsItem.setState(resultSet.getInt("state"));
				iprNewsItem.setContent(resultSet.getString("content"));
				iprNewsItem.setTitle(resultSet.getString("title"));
				iprNewsItem.setUpdatetime(resultSet.getTimestamp("updatetime"));
				iprNewsItem.setCreatetime(resultSet.getTimestamp("createtime"));
				iprNewsItems.add(iprNewsItem);
			}
			page = new Page(size, totalCount);
			page.setData(iprNewsItems);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return page;
	}

	@Override
	public IprNewsItem findById(Integer id) {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		int flag = 0;
		String sql = "select * from `ipr_news_item` where id = " + id;
		IprNewsItem iprNewsItem = new IprNewsItem();
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				iprNewsItem.setId(resultSet.getInt("id"));
				iprNewsItem.setState(resultSet.getInt("state"));
				iprNewsItem.setContent(resultSet.getString("content"));
				iprNewsItem.setType(resultSet.getInt("type"));
				iprNewsItem.setTitle(resultSet.getString("title"));
				iprNewsItem.setUpdatetime(resultSet.getTimestamp("updatetime"));
				iprNewsItem.setCreatetime(resultSet.getTimestamp("createtime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return iprNewsItem;
	}

	@Override
	public boolean edit(NewsEditForm newsEditForm) {
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		int flag = 0;
		String sql = "UPDATE ipr_news_item SET content=?, title=? where id = ?";
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, newsEditForm.getContent());
			preparedStatement.setString(2, newsEditForm.getTitle());
			preparedStatement.setInt(3, newsEditForm.getId());
			flag = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

	@Override
	public boolean deleteNewsItem(Integer id) {
		// TODO Auto-generated method stub
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement preparedStatement = null;
		int flag = 0;
		String sql = "UPDATE `ipr_news_item` SET state=1 where id = ?";
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			flag = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				JDBCUtil.close(conn, preparedStatement);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag > 0 ? true : false;
	}

}
